﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Runtime.Serialization.Formatters.Binary;
using System.Runtime.Serialization;
using System.Collections;

namespace SyncLib
{
    public class SyncPointManager
    {
        IDbConnection cn;
        Guid storeID;
        SqlTransaction transaction;
        Hashtable importData;

        public SyncPointManager(IDbConnection cn, Guid storeID)
        {
            this.cn = cn;
            this.storeID = storeID;
        }

        /// <summary>
        /// Make a DataTable of changed rows in table from history point pointID
        /// </summary>
        /// <param name="table"></param>
        /// <param name="pointID"></param>
        /// <returns>DataTable with name: table</returns>
        private DataTable readChangedTable(String table, DateTime fromTime)
        {
            SqlCommand cmd = (SqlCommand)cn.CreateCommand();
            cmd.CommandText = "GetChangedRows";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@from", SqlDbType.DateTime).Value = fromTime;
            cmd.Parameters.Add("@tablename", SqlDbType.VarChar).Value = table;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;

            // Add the information about the key for the import process
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            DataTable dt = new DataTable(table);
            da.Fill(dt);
            return dt;
        }

        /// <summary>
        /// Make a DataTable of deleted rows' ID in table from history point pointID
        /// </summary>
        /// <param name="table"></param>
        /// <param name="pointID"></param>
        /// <returns>DataTable with name: table."DeletedRows"</returns>
        private DataTable readDeletedTable(String table, DateTime fromTime)
        {
            SqlCommand cmd = (SqlCommand)cn.CreateCommand();
            cmd.CommandText = "GetDeletedRows";
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.Add("@from", SqlDbType.DateTime).Value = fromTime;
            cmd.Parameters.Add("@tablename", SqlDbType.VarChar).Value = table;
            SqlDataAdapter da = new SqlDataAdapter();
            da.SelectCommand = cmd;

            // Add the information about the key for the import process
            da.MissingSchemaAction = MissingSchemaAction.AddWithKey;

            DataTable dt = new DataTable(table + "DeletedRows");
            da.Fill(dt);
            return dt;
        }

        /// <summary>
        /// Create Sync file from last sync point of tables
        /// </summary>
        /// <param name="Filename"></param>
        /// <param name="tables"></param>
        //public void CreateSyncFile(String Filename, String[] tables)
        //{
        //    try
        //    {
        //        cn.Open();
        //        SqlCommand cmdGetCurrent = (SqlCommand)cn.CreateCommand();
        //        cmdGetCurrent.CommandType = CommandType.StoredProcedure;
        //        cmdGetCurrent.CommandText = "CreateHistoryPoint";
        //        Int64 currentPoint = (Int64)cmdGetCurrent.ExecuteScalar();

        //        SqlCommand cmdGetPrevious = (SqlCommand)cn.CreateCommand();
        //        cmdGetPrevious.CommandType = CommandType.StoredProcedure;
        //        cmdGetPrevious.CommandText = "GetPreHistoryPoint";
        //        cmdGetPrevious.Parameters.Add("@pointID", SqlDbType.BigInt).Value = currentPoint;
        //        Int64 previousPoint = (Int64)cmdGetPrevious.ExecuteScalar();

        //        CreateSyncFile(Filename, previousPoint, currentPoint, tables);
        //    }
        //    finally
        //    {
        //        cn.Close();
        //    }
        //}

        /// <summary>
        /// Create Sync file from specific history point pointID of tables
        /// </summary>
        /// <param name="Filename"></param>
        /// <param name="pointID"></param>
        /// <param name="tables"></param>
        //public void CreateSyncFile(String Filename, Int64 pointID, String[] tables)
        //{
        //    try
        //    {
        //        cn.Open();

        //        // Create new History Point and get out current History Point
        //        SqlCommand cmdGetCurrent = (SqlCommand)cn.CreateCommand();
        //        cmdGetCurrent.CommandType = CommandType.StoredProcedure;
        //        cmdGetCurrent.CommandText = "CreateHistoryPoint";
        //        Int64 currentPoint = (Int64)cmdGetCurrent.ExecuteScalar();

        //        CreateSyncFile(Filename, pointID, currentPoint, tables);
        //    }
        //    finally
        //    {
        //        cn.Close();
        //    }
        //}

        public void CreateSyncFile(String Filename, DateTime fromTime, String[] tables)
        {
            Int64 syncFrom = 0, syncTo = 0;
            DataSet ds = new DataSet();

            // All of the data will be serialized
            Hashtable data = new Hashtable();

            IDbCommand cmd = cn.CreateCommand();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "GetHistoryRowBefore";
            IDbDataParameter param = cmd.CreateParameter();
            param.ParameterName = "@beforeTime";
            param.DbType = DbType.DateTime;
            param.Value = fromTime;
            cmd.Parameters.Add(param);

            try
            {
                cn.Open();
                object res = cmd.ExecuteScalar();
                if (res != DBNull.Value)
                {
                    syncFrom = (Int64)res;
                }
            }
            finally
            {
                cn.Close();
            }

            param.Value = DateTime.Now;

            try
            {
                cn.Open();
                object res = cmd.ExecuteScalar();
                if (res != DBNull.Value)
                {
                    syncTo = (Int64)res;
                }
            }
            finally
            {
                cn.Close();
            }

            // Data about sync points for importing
            data.Add("SyncDataFrom", syncFrom);
            data.Add("SyncDataFromTime", fromTime);
            data.Add("SyncDataTo", syncTo);
            data.Add("SyncDataToTime", DateTime.Now);
            data.Add("StoreID", storeID);
            data.Add("DataSet", ds);

            FrmProgess frm = new FrmProgess();
            try
            {
                frm.Title = "Đang tạo báo cáo...";
                frm.ProgressMin = 0;
                frm.ProgressCurrent = 0;
                frm.ProgressMax = tables.Length * 2;
                foreach (String table in tables)
                {
                    frm.ProgressCurrent++;
                    frm.Info = "Đang đọc những dòng được thêm mới và sửa đổi trong bảng " + table + " ...";
                    ds.Tables.Add(readChangedTable(table, fromTime));

                    frm.ProgressCurrent++;
                    frm.Info = "Đang đọc những dòng bị xóa trong bảng " + table + " ...";
                    ds.Tables.Add(readDeletedTable(table, fromTime));
                }

                BinaryFormatter formatter = new BinaryFormatter();

                using (System.IO.FileStream file = new System.IO.FileStream(Filename, System.IO.FileMode.Create, System.IO.FileAccess.Write))
                    formatter.Serialize(file, data);
            }
            finally
            {
                frm.Close();
            }
        }


        public bool CheckSyncPoint()
        {
            return CheckSyncPoint(importData);
        }

        // Check if the file is compatible
        public bool CheckSyncPoint(Hashtable data)
        {
            // check if synced point >= pointFrom
            // set synced point to pointTo
            SqlCommand cmdCheckSyncPoint = (SqlCommand)cn.CreateCommand();
            cmdCheckSyncPoint.CommandType = CommandType.Text;
            cmdCheckSyncPoint.Transaction = transaction;
            cmdCheckSyncPoint.CommandText = "SELECT PointSynced FROM SyncPoint WHERE StoreID = @StoreID";
            cmdCheckSyncPoint.Parameters.Add("@StoreID", SqlDbType.UniqueIdentifier).Value = data["StoreID"];

            Int64 syncedPoint = 1;
            object objSynced = cmdCheckSyncPoint.ExecuteScalar();
            if (objSynced == null) // Haven't had storeID in database yet, create new sync point entry for storeID
            {
                SqlCommand cmdInsertSyncForStoreID = (SqlCommand)cn.CreateCommand();
                cmdInsertSyncForStoreID.CommandType = CommandType.Text;
                cmdInsertSyncForStoreID.Transaction = transaction;
                cmdInsertSyncForStoreID.CommandText = "INSERT INTO SyncPoint VALUES (@StoreID, @SyncedPoint, @syncedTime)";
                cmdInsertSyncForStoreID.Parameters.Add("@StoreID", SqlDbType.UniqueIdentifier).Value = data["StoreID"];
                cmdInsertSyncForStoreID.Parameters.Add("@SyncedPoint", SqlDbType.BigInt).Value = 1;
                cmdInsertSyncForStoreID.Parameters.Add("@syncedTime", SqlDbType.DateTime).Value = new DateTime(1990, 6, 26);
                cmdInsertSyncForStoreID.ExecuteNonQuery();
            }
            else // Already have in database, get information
            {
                syncedPoint = (Int64)objSynced;
            }

            Int64 pointFrom = (Int64)data["SyncDataFrom"];

            if (syncedPoint < pointFrom)
            {
                return false;
            }

            return true;
        }

        private void UpdateOrInsert(DataTable dt, FrmProgess frm)
        {
            Dictionary<DataColumn, SqlParameter> paramOfUpdate = new Dictionary<DataColumn, SqlParameter>();
            Dictionary<DataColumn, SqlParameter> paramOfInsert = new Dictionary<DataColumn, SqlParameter>();
            SqlCommand updateCmd = (SqlCommand)cn.CreateCommand();
            SqlCommand insertCmd = (SqlCommand)cn.CreateCommand();
            insertCmd.CommandType = CommandType.Text;
            insertCmd.Transaction = transaction;
            updateCmd.CommandType = CommandType.Text;
            updateCmd.Transaction = transaction;

            // Create update sql
            StringBuilder updateSQL = new StringBuilder("UPDATE " + dt.TableName + " SET ");
            StringBuilder insertSQL = new StringBuilder("INSERT INTO " + dt.TableName + " (");
            // NON-Key columns
            foreach (DataColumn col in dt.Columns)
            {
                paramOfUpdate.Add(col, new SqlParameter("@" + col.ColumnName, col.DataType));
                paramOfInsert.Add(col, new SqlParameter("@" + col.ColumnName, col.DataType));
                updateCmd.Parameters.Add(paramOfUpdate[col]);
                insertCmd.Parameters.Add(paramOfInsert[col]);

                insertSQL.Append(col.ColumnName + ", ");

                // Current column is a primary key, ignore in update cmd
                //if (dt.PrimaryKey.ToList().IndexOf(col) >= 0)
                //    continue;
                bool primaryKey = false;
                for (int i = 0; i < dt.PrimaryKey.Length; i++)
                {
                    if (dt.PrimaryKey[i] == col)
                    {
                        primaryKey = true;
                    }
                }
                if (primaryKey)
                {
                    continue;
                }
                updateSQL.Append(col.ColumnName + " = @" + col.ColumnName + ", ");
            }

            insertSQL.Append(") VALUES (");

            foreach (DataColumn col in dt.Columns)
            {
                insertSQL.Append("@" + col.ColumnName + ", ");
            }
            insertSQL.Append(")");
            insertSQL.Replace(", )", " )");

            // Key
            updateSQL.Append("WHERE " + dt.PrimaryKey[0].ColumnName + " = @" + dt.PrimaryKey[0].ColumnName);
            for (int i = 1; i < dt.PrimaryKey.Length; i++)
            {
                updateSQL.Append(" AND " + dt.PrimaryKey[i].ColumnName + " = @" + dt.PrimaryKey[i].ColumnName);
            }

            updateSQL.Replace(", WHERE", " WHERE");

            updateCmd.CommandText = updateSQL.ToString();
            insertCmd.CommandText = insertSQL.ToString();

            foreach (DataRow dr in dt.Rows)
            {
                bool ignoreRow = false;
                foreach (DataColumn col in dt.Columns)
                {
                    if (col.ColumnName.Equals("StoreID"))
                    {
                        Guid store = (Guid)dr[col];
                        if (store != storeID && storeID != Guid.Empty)
                            ignoreRow = true;
                    }
                    paramOfUpdate[col].Value = dr[col];
                    paramOfInsert[col].Value = dr[col];
                }

                if (!ignoreRow)
                {
                    int result = updateCmd.ExecuteNonQuery();
                    if (result == 0) // Can't update anything, insert that row to database
                    {
                        insertCmd.ExecuteNonQuery();
                    }
                    frm.ProgressCurrent++;
                }
            }
        }

        private void DeleteRows(DataTable dt, DataTable tableSchema, Guid storeID, FrmProgess frm)
        {
            Dictionary<DataColumn, SqlParameter> paramOf = new Dictionary<DataColumn, SqlParameter>();
            SqlCommand deleteCmd = (SqlCommand)cn.CreateCommand();
            deleteCmd.CommandType = CommandType.Text;
            deleteCmd.Transaction = transaction;

            StringBuilder s = new StringBuilder("DELETE FROM " + tableSchema.TableName + " WHERE " +
                tableSchema.PrimaryKey[0].ColumnName + " = @" + tableSchema.PrimaryKey[0].ColumnName);
            paramOf.Add(tableSchema.PrimaryKey[0], new SqlParameter("@" + tableSchema.PrimaryKey[0].ColumnName,
                tableSchema.PrimaryKey[0].DataType));

            for (int i = 1; i < tableSchema.PrimaryKey.Length; i++)
            {
                paramOf.Add(tableSchema.PrimaryKey[i], new SqlParameter("@" + tableSchema.PrimaryKey[i].ColumnName, tableSchema.PrimaryKey[i].DataType));
                s.Append(" AND " + tableSchema.PrimaryKey[i].ColumnName + " = @" +
                    tableSchema.PrimaryKey[i].ColumnName);
            }

            foreach (DataColumn col in tableSchema.PrimaryKey)
            {
                deleteCmd.Parameters.Add(paramOf[col]);
            }

            foreach (DataRow dr in dt.Rows)
            {
                deleteCmd.CommandText = s.ToString();

                foreach (DataColumn col in tableSchema.PrimaryKey)
                    if (!col.ColumnName.Equals("StoreID"))
                        paramOf[col].Value = dr[0];
                    else
                        paramOf[col].Value = storeID;

                deleteCmd.ExecuteNonQuery();
                frm.ProgressCurrent++;
            }
        }

        private void SetSynced(Hashtable data)
        {
            Int64 pointTo = (Int64)data["SyncDataTo"];
            // Set synced point to pointTo
            SqlCommand updateSyncPoint = (SqlCommand)cn.CreateCommand();
            updateSyncPoint.CommandType = CommandType.Text;
            updateSyncPoint.CommandText = "UPDATE SyncPoint SET PointSynced = @synced, TimeSynced = @timeSynced WHERE StoreID = @StoreID";
            updateSyncPoint.Parameters.Add("@synced", SqlDbType.BigInt).Value = pointTo;
            updateSyncPoint.Parameters.Add("@StoreID", SqlDbType.UniqueIdentifier).Value = data["StoreID"];
            updateSyncPoint.Parameters.Add("@timeSynced", SqlDbType.DateTime).Value = data["SyncDataToTime"];
            updateSyncPoint.Transaction = transaction;

            updateSyncPoint.ExecuteNonQuery();
        }

        private void UnprotectTable(DataTable dt)
        {
            SqlCommand unprotectCmd = (SqlCommand)cn.CreateCommand();
            unprotectCmd.CommandType = CommandType.StoredProcedure;
            unprotectCmd.CommandText = "UnprotectTable";
            unprotectCmd.Parameters.Add("@tableName", SqlDbType.NVarChar, 30).Value = dt.TableName;
            unprotectCmd.Transaction = transaction;

            unprotectCmd.ExecuteNonQuery();
        }

        private void ProtectTable(DataTable dt)
        {
            SqlCommand protectCmd = (SqlCommand)cn.CreateCommand();
            protectCmd.CommandType = CommandType.StoredProcedure;
            protectCmd.CommandText = "ProtectTable";
            protectCmd.Parameters.Add("@tableName", SqlDbType.NVarChar, 30).Value = dt.TableName;
            protectCmd.Transaction = transaction;

            protectCmd.ExecuteNonQuery();
        }

        public void LoadData(string filename)
        {
            importData = new Hashtable();
            BinaryFormatter formatter = new BinaryFormatter();

            using (System.IO.FileStream file = new System.IO.FileStream(filename, System.IO.FileMode.Open, System.IO.FileAccess.Read))
                importData = (Hashtable)formatter.Deserialize(file);
        }

        public void ImportSyncFile(String filename)
        {
            LoadData(filename);
            ImportSyncFile();
        }

        public DataSet getDataSet()
        {
            if (importData == null)
            {
                return null;
            }
            return (DataSet)importData["DataSet"];
        }

        public object getProperty(string key)
        {
            if (importData == null)
            {
                return null;
            }
            return importData[key];
        }

        public void ImportSyncFile()
        {
            try
            {
                cn.Open();
                if (!CheckSyncPoint(importData))
                {
                    //string err = "Hệ thống chỉ mới nhận được báo cáo đến ngày
                    throw new Exception("Current database doesn't compatible with this file.");
                }

                transaction = (SqlTransaction)cn.BeginTransaction();
                FrmProgess frmPro = new FrmProgess();
                frmPro.Title = "Importing Data";
                frmPro.ProgressMin = 0;
                frmPro.ProgressCurrent = 0;


                try
                {
                    DataSet ds = (DataSet)importData["DataSet"];
                    int proMax = 0;
                    foreach (DataTable dt in ds.Tables)
                    {
                        proMax += dt.Rows.Count;
                    }

                    frmPro.ProgressMax = proMax;

                    // Chứa danh sách các table đã xử lý, vì phần delete sẽ xử lý theo chiều ngược so với insert và update
                    // (do quan hệ cha con trên các table
                    Stack<string> tableList = new Stack<string>();

                    foreach (DataTable dt in ds.Tables)
                    {
                        frmPro.Show();
                        if (!dt.TableName.EndsWith("DeletedRows")) // System table (changed rows)
                        {
                            tableList.Push(dt.TableName);
                            try
                            {
                                frmPro.Info = "Updating changed rows and inserting new rows in " + dt.TableName + " ...";
                                UnprotectTable(dt);
                                UpdateOrInsert(dt, frmPro);
                            }
                            finally
                            {
                                if (storeID != (Guid)importData["StoreID"])
                                {
                                    ProtectTable(dt);
                                }
                            }
                        }
                        //else // DeletedRows tables
                    }

                    //Xử lý DeletedRows tables
                    while (tableList.Count > 0)
                    {
                        string tableName = tableList.Pop();
                        DataTable dt = ds.Tables[tableName+"DeletedRows"];
                        DataTable tableSchema = ds.Tables[tableName];
                        try
                        {
                            // Get its former table for information
                            frmPro.Info = "Deleting removed rows in " + dt.TableName.Replace("DeletedRows", "") + " ...";
                            UnprotectTable(tableSchema);
                            DeleteRows(dt, tableSchema, (Guid)importData["StoreID"], frmPro);
                        }
                        finally
                        {
                            if (storeID != (Guid)importData["StoreID"])
                            {
                                ProtectTable(tableSchema);
                            }
                        }

                    }
                    SetSynced(importData);
                    transaction.Commit();
                }
                catch (Exception e)
                {
                    transaction.Rollback();
                    throw e;
                    //// Pause for debug
                    //System.Console.ReadLine();
                }
                finally
                {
                    frmPro.Close();
                }
            }
            finally
            {
                cn.Close();
            }
        }
    }
}
